package com.scalar.chiptrack.operations.reports.dao;

import com.scalar.chiptrack.db.DBTransaction;
import com.scalar.chiptrack.db.DataSource;
//import com.scalar.chiptrack.operations.reports.WorkOrder;
import com.scalar.chiptrack.utils.SqlQueryMappings;
import com.scalar.chiptrack.operations.reports.forms.QADReportsForm;
import com.scalar.chiptrack.operations.reports.WorkOrder;
import com.scalar.ScalarException;

import java.util.LinkedHashMap;
import java.util.ArrayList;
import java.util.HashMap;
import java.sql.*;

/**
 * User: sujankumar.suppala
 * Date: Apr 5, 2006
 * Time: 3:29:49 PM
 */
public class QADReportsDAO
{

    public static ArrayList loadPackageTypes ( DBTransaction dbTransaction ) throws ScalarException
    {

        Connection conn = null;
        PreparedStatement ps = null;
        ResultSet rs = null;
        ArrayList list = new ArrayList();

        try
        {
            if (dbTransaction == null) {
                conn = DataSource.getConnection();
            }
            else {
                conn = dbTransaction.getConnection();
            }

            ps = conn.prepareStatement ( SqlQueryMappings.get ("operations.reports.loadPackageTypes") );
            rs = ps.executeQuery();
            while( rs.next() )
            {
                list.add ( new org.apache.struts.util.LabelValueBean ( rs.getString("PACKAGE_TYPE"), rs.getString("PACKAGE_TYPE") ) );
            }

        }
         catch ( SQLException se ) {
            se.printStackTrace();
            throw new ScalarException ("Could not load all Package Types " , se);
        }

        finally
        {
            if (rs != null) {
                try { rs.close(); } catch (SQLException igonore) { }
            }

            if (ps != null) {
                try { ps.close(); } catch (SQLException igonore) { }
            }

            if (conn != null) {
                try { conn.close(); } catch (SQLException igonore) { }
            }
        }

        return list;
    }

    public static ArrayList loadCardTypes ( DBTransaction dbTransaction ) throws ScalarException
    {

        Connection conn = null;
        PreparedStatement ps = null;
        ResultSet rs = null;
        ArrayList list = new ArrayList();

        try
        {
            if (dbTransaction == null) {
                conn = DataSource.getConnection();
            }
            else {
                conn = dbTransaction.getConnection();
            }

            ps = conn.prepareStatement ( SqlQueryMappings.get ("operations.reports.loadCardTypes") );
            rs = ps.executeQuery();
            while( rs.next() )
            {
                list.add ( new org.apache.struts.util.LabelValueBean ( rs.getString("CARD_TYPE"), rs.getString("CARD_TYPE") ) );
            }

        }
         catch ( SQLException se ) {
            se.printStackTrace();
            throw new ScalarException ("Could not load all Card Types " , se);
        }

        finally
        {
            if (rs != null) {
                try { rs.close(); } catch (SQLException igonore) { }
            }

            if (ps != null) {
                try { ps.close(); } catch (SQLException igonore) { }
            }

            if (conn != null) {
                try { conn.close(); } catch (SQLException igonore) { }
            }
        }

        return list;
    }

    public static ArrayList loadTeraBytes ( DBTransaction dbTransaction ) throws ScalarException
    {

        Connection conn = null;
        PreparedStatement ps = null;
        ResultSet rs = null;
        ArrayList list = new ArrayList();

        try
        {
            if (dbTransaction == null) {
                conn = DataSource.getConnection();
            }
            else {
                conn = dbTransaction.getConnection();
            }

            ps = conn.prepareStatement ( SqlQueryMappings.get ("operations.reports.loadTeraBytes") );
            rs = ps.executeQuery();
            while( rs.next() )
            {
                list.add ( new org.apache.struts.util.LabelValueBean ( rs.getString("TERABYTES"), rs.getString("TERABYTES") ) );
            }

        }
         catch ( SQLException se ) {
            se.printStackTrace();
            throw new ScalarException ("Could not load all Memory Sizes " , se);
        }

        finally
        {
            if (rs != null) {
                try { rs.close(); } catch (SQLException igonore) { }
            }

            if (ps != null) {
                try { ps.close(); } catch (SQLException igonore) { }
            }

            if (conn != null) {
                try { conn.close(); } catch (SQLException igonore) { }
            }
        }

        return list;
    }
    public static LinkedHashMap getWONotStartedPerPlan ( DBTransaction dbTransaction, QADReportsForm qadReportsForm ) throws ScalarException
    {
        Connection conn = null;
        CallableStatement cstmt = null;
        ResultSet rset = null;
        WorkOrder workOrder = null;
        ArrayList workOrders = null;
        LinkedHashMap workOrdersNotStarted = new LinkedHashMap();
        try
        {
            if ( dbTransaction != null )
            {
                conn = dbTransaction.getConnection();
            }
            else
            {
                conn = DataSource.getConnection();
            }
            String processTypeName = qadReportsForm.getProcessTypeName();
            if ( processTypeName != null && "Mark and Pack".equalsIgnoreCase(processTypeName) )
            {
                processTypeName = "Mark & Pack";
            }
            cstmt = conn.prepareCall ( "{CALL SP_WORK_ORDER_NOT_STARTED_PER_PLAN (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)}");
            cstmt.setString( 1, processTypeName );
            cstmt.setInt ( 2, qadReportsForm.getBaseProductId() );
            cstmt.setInt ( 3, qadReportsForm.getProcessStepId() );
            cstmt.setString ( 4, qadReportsForm.getCardType() );
            cstmt.setString ( 5, qadReportsForm.getPackageType() );
            cstmt.setString ( 6, qadReportsForm.getMemorySize() );
            cstmt.setString ( 7, qadReportsForm.getDatePeriod() );
            cstmt.setString ( 8, qadReportsForm.getDueStartDate() );
            cstmt.setString ( 9, qadReportsForm.getDueEndDate() );
            cstmt.setInt    ( 10, 0 );
            cstmt.setString ( 11, "0" );
            rset = cstmt.executeQuery();
            String woDueDate = null;
            while ( rset.next( ) )
            {
                workOrder = new WorkOrder();
                workOrder.setWoNumber( rset.getString ( "WORK_ORDER_NO" ) );
                woDueDate = rset.getString ( "QAD_DUE_DATE");
                workOrder.setWoQtyOpen( rset.getInt ( "QTY_IN" ) );
                workOrder.setWoRelDate( rset.getString( "QAD_REL_DATE" ));
                String minStage = rset.getString( "MIN_STAGE_NAME" );
                workOrder.setStage( ( minStage == null || minStage.trim().length() == 0 ) ? "N/A" : minStage );
                String sod = rset.getString ( "SOD" );
                workOrder.setSod( (sod == null || sod.trim().length()==0) ? "N/A" : sod );
                workOrder.setPartNumber ( rset.getString ( "ITEM_NUMBER" ) );
                workOrders = (ArrayList)workOrdersNotStarted.get(woDueDate);
                if ( workOrders == null )
                {
                    workOrders = new ArrayList();
                    workOrdersNotStarted.put( woDueDate, workOrders );
                }
                workOrders.add ( workOrder );
            }
        }
        catch ( Exception e )
        {
            e.printStackTrace();
            throw new ScalarException ( "Could not get Work Orders Not Started per Plan", e );
        }
        finally
        {
            if ( rset != null )
            {
                try { rset.close(); } catch ( SQLException ignore ) { }
            }
            if ( cstmt != null )
            {
                try { cstmt.close(); } catch ( SQLException ignore ) { }
            }
            if ( conn != null )
            {
                try { conn.close(); } catch ( SQLException ignore ) { }
            }
        }
        return workOrdersNotStarted;
    }
    public static LinkedHashMap getWODueDateVsVendorsCommitDate ( DBTransaction dbTransaction, QADReportsForm qadReportsForm ) throws ScalarException
    {
        LinkedHashMap woDueDateMap  = new LinkedHashMap();
        ArrayList workOdersList     = null;
        WorkOrder workOrder         = null;
        HashMap dueDateSODMap       = null;
        Connection conn     = null;
        CallableStatement cstmt = null;
        ResultSet rset      = null;
        try
        {
            if ( dbTransaction != null )
            {
                conn = dbTransaction.getConnection();
            }
            else
            {
                conn = DataSource.getConnection();
            }
            String processTypeName = qadReportsForm.getProcessTypeName();
            if ( processTypeName != null && "Mark and Pack".equalsIgnoreCase(processTypeName) )
            {
                processTypeName = "Mark & Pack";
            }
            cstmt = conn.prepareCall ( "{CALL SP_WORK_ORDER_NOT_STARTED_PER_PLAN (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)}");
            cstmt.setString( 1, processTypeName );
            cstmt.setInt ( 2, qadReportsForm.getBaseProductId() );
            cstmt.setInt ( 3, qadReportsForm.getProcessStepId() );
            cstmt.setString ( 4, qadReportsForm.getCardType() );
            cstmt.setString ( 5, qadReportsForm.getPackageType() );
            cstmt.setString ( 6, qadReportsForm.getMemorySize() );
            cstmt.setString ( 7, qadReportsForm.getDatePeriod() );
            cstmt.setString ( 8, qadReportsForm.getDueStartDate() );
            cstmt.setString ( 9, qadReportsForm.getDueEndDate() );
            cstmt.setInt    ( 10, 1 );
            cstmt.setString ( 11, "0" );
            rset = cstmt.executeQuery();
            String woDueDate = null;
            String sodFlg;
            double terabytes;
            while ( rset.next() )
            {
                workOrder = new WorkOrder();
                terabytes   = 0;
                woDueDate   = rset.getString ( "QAD_DUE_DATE" );
                sodFlg      = rset.getString ( "QAD_SOD_LATE_DAYS" );

                workOrder.setWoNumber( rset.getString ( "WORK_ORDER_NO" ) );
                workOrder.setWoQtyOpen( rset.getInt ( "QTY_IN" ) );
                workOrder.setWoRelDate( rset.getString( "QAD_REL_DATE" ));
                String minStage = rset.getString( "MIN_STAGE_NAME" );
                workOrder.setStage( ( minStage == null || minStage.trim().length() == 0 ) ? "N/A" : minStage );
                String sod = rset.getString ( "SOD" );
                workOrder.setSod( (sod == null || sod.trim().length()==0) ? "N/A" : sod );
                workOrder.setPartNumber ( rset.getString ( "ITEM_NUMBER" ) );
                workOrder.setShippableMB( rset.getLong ( "SHIPPABLE_MB" ));

                dueDateSODMap = (HashMap)woDueDateMap.get( woDueDate );
                if ( dueDateSODMap == null )
                {
                    dueDateSODMap = new HashMap();
                    dueDateSODMap.put ( "NoSOD", new ArrayList() );
                    dueDateSODMap.put ( "LateSOD", new ArrayList() );
                    dueDateSODMap.put ( "OnTimeSOD", new ArrayList() );
                    dueDateSODMap.put ( "EarlySOD", new ArrayList() );
                    dueDateSODMap.put ( "NoSODTB", "0" );
                    dueDateSODMap.put ( "LateSODTB", "0" );
                    dueDateSODMap.put ( "OnTimeSODTB", "0" );
                    dueDateSODMap.put ( "EarlySODTB", "0" );

                    woDueDateMap.put  ( woDueDate, dueDateSODMap );
                }
                if ( sodFlg == null ) //No SOD
                {
                    workOdersList = (ArrayList)dueDateSODMap.get ( "NoSOD" );
                    try
                    {
                        terabytes = Double.parseDouble((String)dueDateSODMap.get("NoSODTB"));
                    }catch( NumberFormatException e ) {terabytes = 0;}
                    terabytes = terabytes + (workOrder.getShippableMB()/(1024*1024*1.0));
                    dueDateSODMap.put ( "NoSODTB", Double.toString(terabytes) );
                    workOdersList.add ( workOrder );
                }
                else
                {
                    int sodFlgInt = 0;
                    try
                    {
                        sodFlgInt = Integer.parseInt ( sodFlg );
                    }catch ( NumberFormatException e ) { }

                    if ( sodFlgInt  > 0 ) //Late SOD
                    {
                        workOdersList = (ArrayList)dueDateSODMap.get ( "LateSOD" );
                        try
                        {
                            terabytes = Double.parseDouble((String)dueDateSODMap.get("LateSODTB"));
                        }catch( NumberFormatException e ) {terabytes = 0;}
                        terabytes = terabytes + (workOrder.getShippableMB()/(1024*1024*1.0));
                        dueDateSODMap.put ( "LateSODTB", Double.toString(terabytes) );
                        workOdersList.add ( workOrder );
                    }
                    else if ( sodFlgInt == 0 ) //OnTime SOD
                    {
                        workOdersList = (ArrayList)dueDateSODMap.get ( "OnTimeSOD" );
                        try
                        {
                            terabytes = Double.parseDouble((String)dueDateSODMap.get("OnTimeSODTB"));
                        }catch( NumberFormatException e ) {terabytes = 0;}
                        terabytes = terabytes + (workOrder.getShippableMB()/(1024*1024*1.0));
                        dueDateSODMap.put ( "OnTimeSODTB", Double.toString(terabytes) );
                        workOdersList.add ( workOrder );
                    }
                    else if ( sodFlgInt < 0 ) //Early SOD
                    {
                        workOdersList = (ArrayList)dueDateSODMap.get ( "EarlySOD" );
                        try
                        {
                            terabytes = Double.parseDouble((String)dueDateSODMap.get("EarlySODTB"));
                        }catch( NumberFormatException e ) {terabytes = 0;}
                        terabytes = terabytes + (workOrder.getShippableMB()/(1024*1024*1.0));
                        dueDateSODMap.put ( "EarlySODTB", Double.toString(terabytes) );
                        workOdersList.add ( workOrder );
                    }
                }
            }
        }
        catch ( Exception e )
        {
            e.printStackTrace();
            throw new ScalarException ( "Could not get work orders for QAD Due Date Vs Vendor's Commit Date", e );
        }
        finally
        {
            if ( rset != null )
            {
                try { rset.close(); } catch ( SQLException ignore ) { }
            }
            if ( cstmt != null )
            {
                try { cstmt.close(); } catch ( SQLException ignore ) { }
            }
            if ( conn != null )
            {
                try { conn.close(); } catch ( SQLException ignore ) { }
            }
        }
        return woDueDateMap;
    }
    public static LinkedHashMap getStoppedWorkOrders ( DBTransaction dbTransaction, QADReportsForm qadReportsForm ) throws ScalarException
    {
        LinkedHashMap woCommitDateMap  = new LinkedHashMap();
        ArrayList workOdersList     = null;
        WorkOrder workOrder         = null;
        HashMap dueDateSODMap       = null;
        Connection conn     = null;
        CallableStatement cstmt = null;
        ResultSet rset      = null;
        try
        {
            if ( dbTransaction != null )
            {
                conn = dbTransaction.getConnection();
            }
            else
            {
                conn = DataSource.getConnection();
            }
            String processTypeName = qadReportsForm.getProcessTypeName();
            if ( processTypeName != null && "Mark and Pack".equalsIgnoreCase(processTypeName) )
            {
                processTypeName = "Mark & Pack";
            }
            cstmt = conn.prepareCall ( "{CALL SP_WO_STATUS_VS_VENDOR_COMMIT_DATE_REPORT (?, ?, ?, ?, ?, ?, ?, ?, ?, ?)}" );
            cstmt.setString( 1, processTypeName );
            cstmt.setInt ( 2, qadReportsForm.getBaseProductId() );
            cstmt.setInt ( 3, qadReportsForm.getProcessStepId() );
            cstmt.setString ( 4, qadReportsForm.getCardType() );
            cstmt.setString ( 5, qadReportsForm.getPackageType() );
            cstmt.setString ( 6, qadReportsForm.getMemorySize() );
            cstmt.setString ( 7, qadReportsForm.getDatePeriod() );
            cstmt.setString ( 8, qadReportsForm.getDueStartDate() );
            cstmt.setString ( 9, qadReportsForm.getDueEndDate() );
            cstmt.setString ( 10, "0" );
            rset = cstmt.executeQuery();
            String woCommitDate = null;
            int stoppedDays;
            while ( rset.next() )
            {
                workOrder = new WorkOrder();
                woCommitDate   = rset.getString ( "COMMIT_DATE" );
                if ( woCommitDate == null || woCommitDate.trim().length() == 0 )
                {
                    woCommitDate = "N/A";
                }
                stoppedDays      = rset.getInt ( "STOPPED_DAYS" );

                workOrder.setWoNumber( rset.getString ( "WORK_ORDER_NO" ) );
                workOrder.setWoQtyOpen( rset.getInt ( "QTY" ) );
                workOrder.setWoRelDate( rset.getString( "QAD_REL_DATE" ));
                workOrder.setStage( rset.getString( "ACTIVITY" ) );
                workOrder.setPartNumber ( rset.getString ( "ITEM_NUMBER" ) );
                if ( stoppedDays > 0 )
                {
                    dueDateSODMap = (HashMap)woCommitDateMap.get( woCommitDate );
                    if ( dueDateSODMap == null )
                    {
                        dueDateSODMap = new HashMap();
                        dueDateSODMap.put ( "Stopped1Day", new ArrayList() );
                        dueDateSODMap.put ( "Stopped2Days", new ArrayList() );
                        dueDateSODMap.put ( "Stopped3Days", new ArrayList() );
                        dueDateSODMap.put ( "StoppedAbove3Days", new ArrayList() );

                        woCommitDateMap.put  ( woCommitDate, dueDateSODMap );
                    }
                }
                if ( stoppedDays == 1 )
                {
                    workOdersList = (ArrayList)dueDateSODMap.get ( "Stopped1Day" );
                    workOdersList.add ( workOrder );
                }
                else if ( stoppedDays == 2 )
                {
                    workOdersList = (ArrayList)dueDateSODMap.get ( "Stopped2Days" );
                    workOdersList.add ( workOrder );
                }
                else if ( stoppedDays == 3 )
                {
                    workOdersList = (ArrayList)dueDateSODMap.get ( "Stopped3Days" );
                    workOdersList.add ( workOrder );
                }
                else if ( stoppedDays > 3 )
                {
                    workOdersList = (ArrayList)dueDateSODMap.get ( "StoppedAbove3Days" );
                    workOdersList.add ( workOrder );
                }
            }
        }
        catch ( Exception e )
        {
            e.printStackTrace();
            throw new ScalarException ( "Could not load stopped work orders", e );
        }
        finally
        {
            if ( rset != null )
            {
                try { rset.close(); } catch ( SQLException ignore ) { }
            }
            if ( cstmt != null )
            {
                try { cstmt.close(); } catch ( SQLException ignore ) { }
            }
            if ( conn != null )
            {
                try { conn.close(); } catch ( SQLException ignore ) { }
            }
        }
        return woCommitDateMap;
    }
}
